import sqlexecx as db


create_table_sql = '''
CREATE TABLE IF NOT EXISTS person (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER,
  birth_date date DEFAULT NULL,
  sex smallint DEFAULT NULL,
  grade float DEFAULT NULL,
  point decimal(8,2) DEFAULT NULL,
  money decimal(8,4) DEFAULT NULL,
  create_by bigint DEFAULT NULL,
  create_time timestamp DEFAULT CURRENT_TIMESTAMP,
  update_by bigint DEFAULT NULL,
  update_time timestamp DEFAULT NULL,
  del_flag smallint NOT NULL DEFAULT 0
)
'''
select_key = 'SELECT last_insert_rowid()'


def test_select_first():
    """测试 select_first 方法"""
    print("\n=== 测试 select_first 方法 ===")

    # 插入测试数据
    db.save('person', name='张三', age=25)
    db.save('person', name='李四', age=30)
    db.save('person', name='王五', age=35)

    # 测试 select_first - 返回第一行的第一列
    result = db.select_first('select name from person order by age')
    print(f"select_first 返回第一行第一列: {result}")

    # 测试 select_first - 返回符合条件的第一行第一列
    result = db.select_first('select age from person where name = ?', '李四')
    print(f"select_first 指定条件: {result}")

    # 测试 select_first - 无结果时
    result = db.select_first('select name from person where age > 100')
    print(f"select_first 无结果: {result}")


def test_query_first():
    """测试 query_first 方法"""
    print("\n=== 测试 query_first 方法 ===")

    # 确保有测试数据
    if db.get('select count(*) from person') == 0:
        db.save('person', name='张三', age=25)
        db.save('person', name='李四', age=30)
        db.save('person', name='王五', age=35)

    # 测试 query_first - 返回第一行的第一列
    result = db.query_first('select name from person order by age')
    print(f"query_first 返回第一行第一列: {result}")

    # 测试 query_first - 返回符合条件的第一行第一列
    result = db.query_first('select age from person where name = ?', '李四')
    print(f"query_first 指定条件: {result}")

    # 测试 query_first - 无结果时
    result = db.query_first('select name from person where age > 100')
    print(f"query_first 无结果: {result}")


def full_test():
    print(db.save('person', name='zhangsan', age=54))
    print(db.save_sql_select_key(select_key, 'INSERT INTO person(name,age) VALUES(?,?)', 'lisi', 26))
    print(db.insert(table_name='person', name='wangwu', age=38))

    args = [
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56},
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56}
    ]
    db.batch_insert('person', args)
    sql = 'INSERT INTO `person`(`name`,`age`,`birth_date`,`sex`,`grade`,`point`,`money`) VALUES(:name,:age,:birth_date,:sex,:grade,:point,:money)'
    db.batch_execute(sql, args)
    sql = 'INSERT INTO `person`(`name`,`age`,`birth_date`,`sex`,`grade`,`point`,`money`) VALUES(?,?,?,?,?,?,?)'
    args = (('李四', 55, '1968-10-08', 0, 1.0, 20.5, 854.56), ('李四', 55, '1968-10-08', 0, 1.0, 20.5, 854.56))
    db.batch_execute(sql, *args)

    print(db.select('select * from person where name in(?,?)', 'zhangsan', 'lisi'))
    print(db.select_one('select * from person where name = ? limit 1', 'zhangsan'))
    print(db.select_first('select * from person where name = ? limit 1', 'zhangsan'))

    print(db.query('select id, name, age from person'))
    print(db.query_one('select id, name, age from person where name=? limit 1', 'zhangsan'))
    print(db.query_first('select id, name, age from person where name=? limit 1', 'zhangsan'))

    print(db.get('select count(1) from person'))

    # print(db.select_page('select id, name, age from person limit ?,?', 1, 2))
    # print(db.query_page('select id, name, age from person', 1, 2))

    print(db.execute('delete from person where id = ?', 3))


if __name__ == '__main__':
    db.init('test.db', driver='sqlite3', show_sql=True, debug=True)
    db.drop_table('person')
    db.execute(create_table_sql)
    # db.execute('delete from person')
    db.truncate_table('person')

    # 运行原有的完整测试
    full_test()

    # 运行新增的专门测试方法
    test_select_first()
    test_query_first()

    # print(db.query('PRAGMA table_info(person)'))
    print(db.show_tables())
    # db.drop_table('person')
    print(db.sql("select * from person where name like '%%zhangsan%'").query())
    sql = "SELECT * FROM person limit ?, ?"
    print(db.ravel_list(sql, 0, 3))
    print(db.ravel_list(sql, 0, 3, position=1))
    sql = "SELECT * FROM person where age = :age"
    print(db.ravel_list(sql, age=55))
    print(db.ravel_list(sql, age=55, position=1))
    db.table('person').insert_from_pl(db.sql("select name, age, birth_date, sex, grade, point, money from person").load().to_pl())
    db.close()
